'''
Created on 2021年1月15日

@author: Administrator
'''
from flask import json
from peewee import JOIN, fn

from common.public import *
from orm.model import db, WorkOrder, Dept, Document, TransForm, Customer, ServiceDetail, WorkOrderDetails
from view.baseview import BaseView


class WorkOrderView(BaseView):

    def __init__(self):
        pass
  
    def queryWorkOrder(self, params):
        """根据条件取得我的客户列表，params：条件参数"""
        result = []
        SQL = ''
        where_condition = 'True'
        rows = None
        if params["keyword"]:
            likestr = "" + params["keyword"] + ""
            SQL = '''
                AND (C.compname LIKE '{0}' 
                OR C.contacter LIKE '{0}' 
                OR E.document_number = '{0}' 
                OR C.phone LIKE '{0}' 
                OR C.memo LIKE '{0}' 
                OR B.matter LIKE '{0}')
            '''.format(likestr)
            where_condition = where_condition + SQL
        if params["servicename"]:
            SQL = '''
                AND A.name = '{0}'
            '''.format(str(params['servicename']))
            where_condition = where_condition + SQL
        if params["taxtype"]:
            SQL = '''
                AND C.taxtype like '{0}'
            '''.format(params["taxtype"])
            where_condition = where_condition + SQL
        if params["endTime"]:
            SQL = '''
                    AND A.enddate >= '{0}' 
                    AND A.enddate <= '{1}' 
            '''.format(params["endTime"][0] + " 00:00:00",params["endTime"][1] + " 23:59:59")
            where_condition = where_condition + SQL
        if params["transdate"]:
            SQL = '''
                    AND F.transdate >= '{0}'
                    AND F.transdate <= '{1}'
            '''.format(params["transdate"][0] + " 00:00:00",params["transdate"][1] + " 23:59:59")
            where_condition = where_condition + SQL
        if params["updateTime"]:
            SQL = '''
                    AND A.update_time >= '{0}' 
                    AND A.update_time <= '{1}' 
            '''.format(params["updateTime"][0] + " 00:00:00",params["updateTime"][1] + " 23:59:59")
            where_condition = where_condition + SQL
        if params['charger']:
            SQL = '''
                    AND ( A.leader_id IN {0} OR B.charger_id IN {0}) 
            '''.format(str(params['charger']))
            where_condition = where_condition + SQL.replace('[','(').replace(']',')')
        where_condition_status = 'True'
        where_condition_having = ''
        if params['status']:
            SQL = '''
                    B.`status` = '{0}'  
            '''.format(KEEPINGING_AGENT_STATUS_SUCCESS)
            where_condition_status = SQL
            if params['status'] == KEEPINGING_AGENT_STATUS_SUCCESS:
                where_condition_having = '''
                    HAVING
                        ( count = done ) 
                '''
            else:
                where_condition_having = '''
                    HAVING
                        ( count <> done ) 
                '''
        else:
            where_condition_having = ''
        SQL = '''
            SELECT
                id,
                sum( count ) count,
                sum( done ) done 
            FROM
                (
                (SELECT
                    a.id,
                    count( a.id ) count,
                    0 done,A.create_time
                FROM
                    workorder A
                    LEFT JOIN workorder_details B ON A.id = B.workorder_id
                    LEFT JOIN Customer C ON C.id = A.customer_id
                    LEFT JOIN ServiceDetail D ON D.id = A.servicedetail_id
                    LEFT JOIN Document E ON E.id = A.document_id 
                    LEFT JOIN TransForm F ON F.id = A.transform_id 
                WHERE
                    {0} 
                GROUP BY
                    A.id 
                )
                UNION ALL
                (
                SELECT
                    A.id,
                    0 count,
                    count( B.STATUS ) done ,A.create_time
                FROM
                    workorder A
                    LEFT JOIN workorder_details B ON A.id = B.workorder_id
                    LEFT JOIN Customer C ON C.id = A.customer_id
                    LEFT JOIN ServiceDetail D ON D.id = A.servicedetail_id
                    LEFT JOIN Document E ON E.id = A.document_id 
                    LEFT JOIN TransForm F ON F.id = A.transform_id 
                WHERE
                    {0} AND {1}  
                GROUP BY
                    A.id,
                    B.STATUS 
                ) 
                ) H 
            GROUP BY
                id 
            {2} 
        '''.format(where_condition,where_condition_status,where_condition_having)
        print(SQL)
        rows = WorkOrder.raw(SQL)
        total = len(rows)
        # 分页
        SQL = SQL + ''' 
            ORDER BY
                create_time DESC 
                LIMIT {0},
                {1}
        '''.format((int(params["pageNo"])-1)*params["pagesize"],params["pagesize"])
        rows = WorkOrder.raw(SQL)
        for workorder in rows:
            row = WorkOrder.select().join(Customer, JOIN.LEFT_OUTER, on=(WorkOrder.customer_id == Customer.id)) \
                .join(Document, JOIN.LEFT_OUTER, on=(WorkOrder.document_id == Document.id)) \
                .join(TransForm, JOIN.LEFT_OUTER, on=(WorkOrder.transform_id == TransForm.id)) \
                .where(WorkOrder.id == workorder.id).first()
            print("SQL = ",row)
            newmatter = ""
            newstatus = ""
            # 取得最新更新的事项
            updateworkdetailfirstrow = WorkOrderDetails.select().where(
                WorkOrderDetails.workorder_id == workorder.id).order_by(WorkOrderDetails.update_time.desc()).first()
            if updateworkdetailfirstrow != None:
                newmatter = updateworkdetailfirstrow.matter
                newstatus = updateworkdetailfirstrow.status
            if row.document_id is None:
                documentNumber=""
                taxGroup = 0
                taxGroupName = ""
            else:
                documentNumber =row.document.document_number
                deptrow = Dept.select().where(Dept.id == row.document.tax_group).first()
                taxGroup = 0
                taxGroupName = ""
                if deptrow != None:
                    taxGroup = deptrow.id
                    taxGroupName = deptrow.name


            details = WorkOrderDetails.select().where(WorkOrderDetails.workorder_id == row.id)
            recs = []
            for detail in details:
                recs.append({"id": detail.id,
                             "workorderId": detail.workorder_id,
                             "chargerId": detail.charger_id,
                             "charger": detail.charger.username,
                             "matter": detail.matter,
                             "status": detail.status,
                             "remake": detail.remake,
                             "evidence": json.loads(detail.evidence),
                             "endTime": str(detail.end_time),
                             "updateTime": str(detail.update_time),
                             "createTime": str(detail.create_time)
                             })
            transdate = ""
            if row.transform_id != 0:
                transdate = str(row.transform.transdate)
            data = {
                "id": row.id,
                "servicedetailId": row.servicedetail_id,
                "taxtype": row.customer.taxtype,
                "leader": row.leader.username,
                "leaderId": row.leader.id,
                "transformId": row.transform_id,
                "matter": newmatter,
                "status": newstatus,
                "name": row.name,
                "customer_id": row.customer.id,
                "compname": row.customer.compname,
                "contacter": row.customer.contacter,
                "phone": row.customer.phone,
                "remake": row.remake,
                "contractNumber": row.contract_number,
                "documentNumber": documentNumber,
                "taxGroup": taxGroup,
                "taxGroupName": taxGroupName,
                "transdate": transdate,
                "createTime": str(row.create_time),
                "updateTime": str(row.update_time),
                "enddate": str(row.enddate),
                "recs": recs

            }
            result.append(data)
        return super().resultSet(200, "", result, total)
    @db.atomic()                   
    def addWorkOrder(self, params):
        """
      新增
        """
        print(params)
        WorkOrder.create(
            leader_id = params["leaderId"],
            enddate = params['enddate'],
            remake = params['remake'],
            name = params['name'],
            transform_id = 0,
            servicedetail_id = params['servicedetailId'],
            customer_id = params['customerId'],
            update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
            create_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            )
        return super().resultSet(200, "添加成功", [])
        raise Exception("添加失败")

    @db.atomic()                   
    def distributeWorkOrder(self, params):
        """
        更新
        """
        # 更新负责人期限
        workorder = WorkOrder.get(WorkOrder.id == params["id"])
        workorder.contract_number = params["contractNumber"]
        workorder.enddate = params["enddate"]
        workorder.leader_id = params["leaderId"]
        workorder.remake = params["remake"]
        document, created = Document.get_or_create(customer_id = workorder.customer_id, defaults = {'customer_id': workorder.customer_id})
        # document = Document.get(workorder.document_id == Document.id)
        document.document_number = params["documentNumber"]
        document.tax_group = params["taxGroup"]
        workorder.document_id = document.id
        workorder.save()
        document.save()
        return super().resultSet(200, "更新成功", [])
        raise Exception("更新失败")

    @db.atomic()                   
    def addWorkOrderItem(self, params):
        """
      新增
        """
        params["evidence"] = str(params["evidence"]).replace("'", "\"").replace(r"\n", "")
        WorkOrderDetails.create(
            workorder_id = params["workorderId"],
            charger_id = params["chargerId"],
            matter = params["matter"],
            status = params["status"],
            remake = params["remake"],
            evidence = params["evidence"],
            end_time = params["endTime"],
            update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
            create_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            )
        return super().resultSet(200, "添加成功", [])
        raise Exception("添加失败")

    @db.atomic()                   
    def editWorkOrderItem(self, params):
        """
      修改
        """
        print(params)
        params["evidence"] = str(params["evidence"]).replace("'", "\"").replace(r"\n", "")
        workorderdetail = WorkOrderDetails.get(WorkOrderDetails.id == params["id"])
        workorderdetail.status = params["status"]
        workorderdetail.end_time = params["endTime"]
        workorderdetail.charger_id = params["chargerId"]
        workorderdetail.matter = params["matter"]
        workorderdetail.remake = params["remake"]
        workorderdetail.evidence = params["evidence"]
        workorderdetail.save()
        return super().resultSet(200, "更新成功", [])
        raise Exception("更新失败")

    @db.atomic()                   
    def removeWorkOrderItem(self, params):
        """
      删除详情
        """
        print(params)
        workorderdetail = WorkOrderDetails.get(WorkOrderDetails.id == params["id"])
        workorderdetail.delete_instance()
        return super().resultSet(200, "删除成功", [])
        raise Exception("更新失败")

    @db.atomic()                   
    def removeMulWorkOrder(self, params):
        """
      多选删除
        """
        print(params["ids"])
        WorkOrderDetails.delete().where(WorkOrderDetails.workorder_id.in_(params["ids"])).execute()
        WorkOrder.delete().where(WorkOrder.id.in_(params["ids"])).execute()
        return super().resultSet(200, "删除成功", [])
        raise Exception("更新失败")
